﻿using System;
using System.Collections.Generic;
using System.Linq;

namespace Red.Launcher.ViewModels.Workspaces.Database.Sql.Old
{
    public class Select : Query
    {
        #region Public Types

        public enum JoinType
        {
            Left,
            Inner,
            Right
        }

        #endregion Public Types

        #region Private Types

        private struct Join
        {
            public JoinType JoinType { get; set; }
            public string Table { get; set; }
            public Record Constraint { get; set; }
        }

        private struct NullCondition
        {
            public string Table { get; set; }
            public string Column { get; set; }
        }

        #endregion Private Types

        #region Private Data

        private List<Join> _joins = new List<Join>();
        private List<NullCondition> _nullConditions = new List<NullCondition>();
        private List<string> _columns = new List<string>() { @"*" };

        private Metadata _metadata;

        #endregion Private Data

        #region Public Functions

        public static string GetTableAlias( Metadata metadata )
        {
            return GetTableAlias( metadata.Table );
        }

        public static string GetTableAlias( string tableName )
        {
            return tableName.ToLower() + @"_alias";
        }

        #endregion Public Functions

        #region Public Methods

        public void AddJoin( string table, Record constraint, JoinType joinType = JoinType.Left )
        {
            _joins.Add
            (
                new Join()
                {
                    Table = table,
                    Constraint = constraint,
                    JoinType = joinType
                }
            );
        }

        public void AddNullCondition( string table, string column)
        {
            _nullConditions.Add
            (
                new NullCondition()
                {
                    Table = table,
                    Column = column
                }
            );
        }

        public void AddCustomColumn( string table, string column, string alias )
        {
            string customColumn = $"{GetTableAlias( table )}.{column} as {alias}";

            _columns.Add( customColumn );
        }

        #endregion Public Methods

        #region Public Properties

        public string SelectSql => $"SELECT {ColumnSql} FROM [{Settings.DatabaseName}].[dbo].[{_metadata.Table}] as {GetTableAlias( _metadata )}\n";

        public string ColumnSql => string.Join( ", ", _columns );

        public string JoinSql
        {
            get
            {
                return string.Join
                (
                    "\n",
                    from j in _joins select string.Format
                    (
                        @"{0} JOIN [{7}].[dbo].[{1}] as {2} on {2}.{3} = {4}.id AND {2}.{5} = '{6}'",
                        j.JoinType.ToString().ToUpper(),
                        j.Table,
                        GetTableAlias( j.Table ),
                        _metadata.ForeignKey,
                        GetTableAlias( _metadata ),
                        j.Constraint.Metadata.ForeignKey,
                        j.Constraint.Id,
                        Settings.DatabaseName
                    )
                );
            }
        }

        public string WhereSql
        {
            get
            {
                int numConditions = Parents.Count + _nullConditions.Count;

                if ( numConditions > 0 )
                {
                    int currentIndex = 0;
                    string[] formattedConditions = new string[ numConditions ];

                    foreach ( Record parent in Parents )
                    {
                        formattedConditions[ currentIndex] = $"{GetTableAlias( _metadata )}.{parent.Metadata.ForeignKey} = '{parent.Id}'";
                        ++currentIndex;
                    }
                    
                    foreach ( NullCondition condition in _nullConditions )
                    {
                        formattedConditions[ currentIndex ] = $"{GetTableAlias( condition.Table )}.{condition.Column} IS NULL";
                        ++currentIndex;
                    }


                    return "\nWHERE " + string.Join( " AND ", formattedConditions );
                }

                return "";
            }
        }

        public string Sql => SelectSql + JoinSql + WhereSql;

        #endregion Public Properties

        #region C-Tor

        public Select( Metadata metadata ) : base( null )
        {
            _metadata = metadata;
        }

        #endregion C-Tor
    }
}
